package cn.itcast.jsjwl.dao;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import cn.itcast.jsjwl.domain.Source;
import cn.itcast.jsjwl.utils.JDBCUtils;

public class SourceDao {

	public  List<Source> findPreSource() throws SQLException {
		String sql="SELECT * FROM upload_file_info ORDER BY uptime DESC LIMIT 0,5";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanListHandler<Source>(Source.class));
	
	}

	public List<Source> findSourceWithPage(int i, int j) throws SQLException {
		String sql="SELECT *FROM upload_file_info LIMIT ?,?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanListHandler<Source>(Source.class),i,j);
	}

	public static int findTotalRecords() throws SQLException {
		String sql="SELECT COUNT(*) FROM upload_file_info";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		Long num=(Long)qr.query(sql, new ScalarHandler());
		return num.intValue();
	}

	public Source findSourceBySid(String sId) throws SQLException {
		String sql="SELECT * FROM upload_file_info WHERE _file_id=?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanHandler<Source>(Source.class),sId);
	}

	public List<Source> findSourceWithPageByTeacher(int startIndex, int pageSize) throws SQLException {
		String sql="SELECT *FROM upload_file_info LIMIT ?,?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanListHandler<Source>(Source.class),startIndex,pageSize);
	}

	public void deleteSourceByTeacher(String sId) throws SQLException {
		String sql="delete FROM upload_file_info WHERE _file_id=?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		qr.update(sql,sId);
	}

	public void addSource(Source source) throws SQLException {
		String sql="INSERT INTO upload_file_info VALUES(null, ? , ? , ? , ? , ? , ?)";
		Object[] params= {source.getFilename(),source.getUname(),source.getNow_filename(),source.getPre_filename(),source.getUptime(),source.getFile_type()};
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		qr.update(sql,params);
	}

	public void deleteSourceByAdmin(String sId) throws SQLException {
		String sql="delete FROM upload_file_info WHERE _file_id=?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		qr.update(sql,sId);
		
	}

	public List<Source> findsourceWithPageByAdmin(int i, int j) throws SQLException {
		String sql="SELECT *FROM upload_file_info LIMIT ?,?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanListHandler<Source>(Source.class),i,j);
	}

	


}
